package com.iris.live.services.report.sources;

import com.iris.live.services.data.repositories.ReportRepository;
import com.iris.live.services.report.Cell;
import com.iris.live.services.report.IExcelReportSource;
import com.iris.live.services.report.Row;
import com.iris.live.services.report.Sheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by 林平 on 2016/12/12.
 */
@Component("GroupSaleMonthlyReportSource")
public class GroupSaleMonthlyReportSource implements IExcelReportSource {
    private static final Logger logger = LoggerFactory.getLogger(GroupSaleMonthlyReportSource.class);

    private static final String[] indicators = {"MB-signedWholesaleTa", "MB-actualWholesale", "formular", "MB-signedRetailTa", "MB-actualRetailNbi", "formular", "MB-rosRetailTa",
            "MB-actualRetailRos", "formular", "MM-salesGuideline", "MM-actualWholesale", "formular", "MM-actualRetailNbi", "formular",
            "MM-rosRetailTa", "MM-actualRetailRos", "formular", "AMG-signedWholesaleTa", "AMG-actualWholesale", "formular", "AMG-signedRetailTa",
            "AMG-actualRetailNbi", "formular", "AMG-rosRetailTa", "AMG-actualRetailRos", "formular", "formular", "formular", "formular",
            "smart-signedRetailTa", "smart-actualWholesale", "formular", "smart-actualRetailNbi", "formular", "smart-rosRetailTa",
            "smart-actualRetailRos", "formular", "", "MB-lt89DaysStock", "MB-daysStock90149", "MB-get150DaysStock", "formular", "formular",
            "formular", "formular", "MM-lt89DaysStock", "MM-daysStock90149", "MM-get150DaysStock", "formular", "formular", "formular", "formular",
            "AMG-lt89DaysStock", "AMG-daysStock90149", "AMG-get150DaysStock", "formular", "formular", "formular", "formular", "formular",
            "formular", "formular", "formular", "formular", "smart-lt89DaysStock", "smart-daysStock90149", "smart-get150DaysStock", "formular",
            "formular", "formular", "formular", "", "", "MB-newCarExternalSalesRevenue", "MB-actualGpi", "MB-actual15", "MB-actualGpii",
            "MB-actualSi", "formular", "formular", "formular", "formular", "formular", "formular", "MM-newCarExternalSalesRevenue", "MM-actualGpi",
            "MM-actual15", "MM-actualGpii", "MM-actualSi", "formular", "formular", "formular", "formular", "formular", "formular",
            "AMG-newCarExternalSalesRevenue", "AMG-actualGpi", "AMG-actual15", "AMG-actualGpii", "AMG-actualSi", "formular", "formular",
            "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular",
            "formular", "formular", "formular", "formular", "smart-newCarExternalSalesRevenue", "smart-actualGpi", "smart-actual15",
            "smart-actualGpii", "smart-actualSi", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular",
            "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "EXPENSES-budgetSalesDirectExpenses",
            "EXPENSES-actualSalesDirectExpenses", "formular", "EXPENSES-budgetSalesIndirectExpenses", "EXPENSES-actualSalesIndirectExpenses",
            "formular", "formular", "", "", "MB-usedCarAppraisalVolume", "formular", "MB-usedCarTradeInVolume", "formular", "MB-tradeInCommission",
            "formular", "MB-afterMarketProductRevenue", "formular", "MB-afterMarketProductGp", "formular", "MB-newCarInsurance", "formular",
            "MB-newCarInsuranceCommission", "MB-newCarFinancing", "formular", "MB-newCarAgility", "formular", "MB-financingCommission",
            "MB-financingServiceFeeMargin", "formular", "MB-registrationServiceFeeMargin", "MB-otherServiceFeeMargin", "formular", "formular",
            "MM-usedCarAppraisalVolume", "formular", "MM-usedCarTradeInVolume", "formular", "MM-tradeInCommission", "formular", "MM-afterMarketProductRevenue",
            "formular", "MM-afterMarketProductGp", "formular", "MM-newCarInsurance", "formular", "MM-newCarInsuranceCommission", "MM-newCarFinancing",
            "formular", "MM-newCarAgility", "formular", "MM-financingCommission", "MM-financingServiceFeeMargin", "formular", "MM-registrationServiceFeeMargin",
            "MM-otherServiceFeeMargin", "formular", "formular", "AMG-usedCarAppraisalVolume", "formular", "AMG-usedCarTradeInVolume", "formular",
            "AMG-tradeInCommission", "formular", "AMG-afterMarketProductRevenue", "formular", "AMG-afterMarketProductGp", "formular", "AMG-newCarInsurance",
            "formular", "AMG-newCarInsuranceCommission", "AMG-newCarFinancing", "formular", "AMG-newCarAgility", "formular", "AMG-financingCommission",
            "AMG-financingServiceFeeMargin", "formular", "AMG-registrationServiceFeeMargin", "AMG-otherServiceFeeMargin", "formular", "formular", "formular",
            "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular",
            "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular",
            "smart-usedCarAppraisalVolume", "formular", "smart-usedCarTradeInVolume", "formular", "smart-tradeInCommission", "formular",
            "smart-afterMarketProductRevenue", "formular", "smart-afterMarketProductGp", "formular", "smart-newCarInsurance", "formular",
            "smart-newCarInsuranceCommission", "smart-newCarFinancing", "formular", "smart-newCarAgility", "formular", "smart-financingCommission",
            "smart-financingServiceFeeMargin", "formular", "smart-registrationServiceFeeMargin", "smart-otherServiceFeeMargin", "formular", "formular",
            "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular",
            "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular", "formular",
            "formular", "formular", "", "", "MB-salesDeptHeadcount", "formular", "smart-salesDeptHeadcount", "formular"};

    private static String[] sheetNameArray = {"E2","NJNX","NJLZX","NJJBZX","YZ","DY","XZLSH","SZ","SZYX","KS","WJ","WX","JY","YX","NT","NTLX"};
    private static String[] permitNameArray = {"E2","NJNX","NJLZX_D","NJJBZX","YZLZX","DYS_D","XZLSH","SZLX_D","SZYX","KSES","WJS","WXDX","JYS","YXS","NTS_D","NTLX"};

    @Autowired
    private ReportRepository reportRepository;

    @Override
    public Sheet[] getSheets(Map<String, Object> context) {
        Sheet[] sheets = new Sheet[16];
        for (int i = 0; i < sheetNameArray.length; i++) {
            Sheet sheet = new Sheet(permitNameArray[i], sheetNameArray[i], true);
            sheets[i] = sheet;
        }
        return sheets;
    }

    @Override
    public List<Row> getSheetRows(Map<String, Object> context, Sheet sheet) {
        String year = context.get("year").toString();
        Map values = null;
        List<Row> rows = new ArrayList<>();

        if (!"E2".equals(sheet.getName())) {
            values = getDataReport(sheet.getCode(), year);

            rows.add(new Row());
            Cell[] cells_2 = new Cell[2];
            cells_2[0] = new Cell(sheet.getName(), 3);
            cells_2[1] = new Cell(year, 6);
            rows.add(new Row(cells_2));

            rows.add(new Row());
            rows.add(new Row());

            for (String indicator : indicators) {
                if (indicator == null || "".equals(indicator)) {
                    rows.add(new Row(indicator));
                } else if (indicator.equals("formular")) {
                    rows.add(new Row(indicator, true));
                } else {
                    rows.add(new Row(indicator, getCells(values, indicator)));
                }
            }
        }
        return rows;
    }

    private Cell[] getCells(Map values, String indicator) {
        Cell[] cells = new Cell[17];
        String value = null;
        int total = 0, q1 = 0, q2 = 0, q3 = 0, q4 = 0;
        for (int index = 1; index <= 12; index++) {
            Object o = values.get(indicator + "-" + (index > 9 ? index : "0" + index));
            value = (o == null ? "0" : o).toString();
            cells[index - 1] = new Cell(value, "double", 4 + index);

            total += (value == null ? 0 : Double.parseDouble(value));
            if (index >= 1 && index <= 3) {
                q1 += (value == null ? 0 : Double.parseDouble(value));
            } else if (index >= 4 && index <= 6) {
                q2 += (value == null ? 0 : Double.parseDouble(value));
            }
            if (index >= 7 && index <= 9) {
                q3 += (value == null ? 0 : Double.parseDouble(value));
            }
            if (index >= 10 && index <= 12) {
                q4 += (value == null ? 0 : Double.parseDouble(value));
            }

        }
        cells[12] = new Cell(total, "double", 17);
        cells[13] = new Cell(q1, "double", 18);
        cells[14] = new Cell(q2, "double", 19);
        cells[15] = new Cell(q3, "double", 20);
        cells[16] = new Cell(q4, "double", 21);
        return cells;
    }

    private Map getDataReport(String type, String year) {
        Map map = new HashMap();
        try {
            List list = getReportByPermitAndYear(type, year);
            for (Object o : list) {
                Map m = (Map) o;
                map.put(m.get("MODEL") + "-signedWholesaleTa-" + m.get("STAT_MONTH"), m.get("SIGNED_WHOLESALE_TA"));
                map.put(m.get("MODEL") + "-actualWholesale-" + m.get("STAT_MONTH"), m.get("ACTUAL_WHOLESALE"));
                map.put(m.get("MODEL") + "-wsAchievement-" + m.get("STAT_MONTH"), m.get("WS_ACHIEVEMENT"));
                map.put(m.get("MODEL") + "-signedRetailTa-" + m.get("STAT_MONTH"), m.get("SIGNED_RETAIL_TA"));
                map.put(m.get("MODEL") + "-actualRetailNbi-" + m.get("STAT_MONTH"), m.get("ACTUAL_RETAIL_NBI"));
                map.put(m.get("MODEL") + "-nbiRtAchievement-" + m.get("STAT_MONTH"), m.get("NBI_RT_ACHIEVEMENT"));
                map.put(m.get("MODEL") + "-rosRetailTa-" + m.get("STAT_MONTH"), m.get("ROS_RETAIL_TA"));
                map.put(m.get("MODEL") + "-actualRetailRos-" + m.get("STAT_MONTH"), m.get("ACTUAL_RETAIL_ROS"));
                map.put(m.get("MODEL") + "-rosRtAchievement-" + m.get("STAT_MONTH"), m.get("ROS_RT_ACHIEVEMENT"));
                map.put(m.get("MODEL") + "-remainingPreReport-" + m.get("STAT_MONTH"), m.get("REMAINING_PRE_REPORT"));
                map.put(m.get("MODEL") + "-salesGuideline-" + m.get("STAT_MONTH"), m.get("SALES_GUIDELINE"));
                map.put(m.get("MODEL") + "-lt89DaysStock-" + m.get("STAT_MONTH"), m.get("LT_89_DAYS_STOCK"));
                map.put(m.get("MODEL") + "-daysStock90149-" + m.get("STAT_MONTH"), m.get("90_149_DAYS_STOCK"));
                map.put(m.get("MODEL") + "-get150DaysStock-" + m.get("STAT_MONTH"), m.get("GET_150_DAYS_STOCK"));
                map.put(m.get("MODEL") + "-closingStock-" + m.get("STAT_MONTH"), m.get("CLOSING_STOCK"));
                map.put(m.get("MODEL") + "-stockLevel-" + m.get("STAT_MONTH"), m.get("STOCK_LEVEL"));
                map.put(m.get("MODEL") + "-agingStockGt90Days-" + m.get("STAT_MONTH"), m.get("AGING_STOCK_GT_90_DAYS"));
                map.put(m.get("MODEL") + "-agingStockGt90DaysPer-" + m.get("STAT_MONTH"), m.get("AGING_STOCK_GT_90_DAYS_PER"));
                map.put(m.get("MODEL") + "-newCarExternalSalesRevenue-" + m.get("STAT_MONTH"), m.get("NEW_CAR_EXTERNAL_SALES_REVENUE"));
                map.put(m.get("MODEL") + "-actualGpi-" + m.get("STAT_MONTH"), m.get("ACTUAL_GPI"));
                map.put(m.get("MODEL") + "-actualGpii-" + m.get("STAT_MONTH"), m.get("ACTUAL_GPII"));
                map.put(m.get("MODEL") + "-actualSi-" + m.get("STAT_MONTH"), m.get("ACTUAL_SI"));
                map.put(m.get("MODEL") + "-actualGpiii-" + m.get("STAT_MONTH"), m.get("ACTUAL_GPIII"));
                map.put(m.get("MODEL") + "-gpiPer-" + m.get("STAT_MONTH"), m.get("GPI_PER"));
                map.put(m.get("MODEL") + "-gpiiPer-" + m.get("STAT_MONTH"), m.get("GPII_PER"));
                map.put(m.get("MODEL") + "-siPer-" + m.get("STAT_MONTH"), m.get("SI_PER"));
                map.put(m.get("MODEL") + "-gpiiiPer-" + m.get("STAT_MONTH"), m.get("GPIII_PER"));
                map.put(m.get("MODEL") + "-budgetSalesDirectExpenses-" + m.get("STAT_MONTH"), m.get("BUDGET_SALES_DIRECT_EXPENSES"));
                map.put(m.get("MODEL") + "-actualSalesDirectExpenses-" + m.get("STAT_MONTH"), m.get("ACTUAL_SALES_DIRECT_EXPENSES"));
                map.put(m.get("MODEL") + "-actualDirectExpensesPerCar-" + m.get("STAT_MONTH"), m.get("ACTUAL_DIRECT_EXPENSES_PER_CAR"));
                map.put(m.get("MODEL") + "-budgetSalesIndirectExpenses-" + m.get("STAT_MONTH"), m.get("BUDGET_SALES_INDIRECT_EXPENSES"));
                map.put(m.get("MODEL") + "-actualSalesIndirectExpenses-" + m.get("STAT_MONTH"), m.get("ACTUAL_SALES_INDIRECT_EXPENSES"));
                map.put(m.get("MODEL") + "-actualIndirectExpensesPerCar-" + m.get("STAT_MONTH"), m.get("ACTUAL_INDIRECT_EXPENSES_PER_CAR"));
                map.put(m.get("MODEL") + "-actualTotalExpensesPerCar-" + m.get("STAT_MONTH"), m.get("ACTUAL_TOTAL_EXPENSES_PER_CAR"));
                map.put(m.get("MODEL") + "-usedCarAppraisalVolume-" + m.get("STAT_MONTH"), m.get("USED_CAR_APPRAISAL_VOLUME"));
                map.put(m.get("MODEL") + "-appraisal-" + m.get("STAT_MONTH"), m.get("APPRAISAL"));
                map.put(m.get("MODEL") + "-usedCarTradeInVolume-" + m.get("STAT_MONTH"), m.get("USED_CAR_TRADE_IN_VOLUME"));
                map.put(m.get("MODEL") + "-tradeInPer-" + m.get("STAT_MONTH"), m.get("TRADE_IN_PER"));
                map.put(m.get("MODEL") + "-tradeInCommission-" + m.get("STAT_MONTH"), m.get("TRADE_IN_COMMISSION"));
                map.put(m.get("MODEL") + "-tradeInCommissionPerCar-" + m.get("STAT_MONTH"), m.get("TRADE_IN_COMMISSION_PER_CAR"));
                map.put(m.get("MODEL") + "-afterMarketProductRevenue-" + m.get("STAT_MONTH"), m.get("AFTER_MARKET_PRODUCT_REVENUE"));
                map.put(m.get("MODEL") + "-afterMarketProductRevenuePerCar-" + m.get("STAT_MONTH"), m.get("AFTER_MARKET_PRODUCT_REVENUE_PER_CAR"));
                map.put(m.get("MODEL") + "-afterMarketProductGp-" + m.get("STAT_MONTH"), m.get("AFTER_MARKET_PRODUCT_GP"));
                map.put(m.get("MODEL") + "-afterMarketProductGpPer-" + m.get("STAT_MONTH"), m.get("AFTER_MARKET_PRODUCT_GP_PER"));
                map.put(m.get("MODEL") + "-newCarInsurance-" + m.get("STAT_MONTH"), m.get("NEW_CAR_INSURANCE"));
                map.put(m.get("MODEL") + "-newCarInsuranceRate-" + m.get("STAT_MONTH"), m.get("NEW_CAR_INSURANCE_RATE"));
                map.put(m.get("MODEL") + "-newCarInsuranceCommission-" + m.get("STAT_MONTH"), m.get("NEW_CAR_INSURANCE_COMMISSION"));
                map.put(m.get("MODEL") + "-newCarFinancing-" + m.get("STAT_MONTH"), m.get("NEW_CAR_FINANCING"));
                map.put(m.get("MODEL") + "-financingRate-" + m.get("STAT_MONTH"), m.get("FINANCING_RATE"));
                map.put(m.get("MODEL") + "-newCarAgility-" + m.get("STAT_MONTH"), m.get("NEW_CAR_AGILITY"));
                map.put(m.get("MODEL") + "-agilityRate-" + m.get("STAT_MONTH"), m.get("AGILITY_RATE"));
                map.put(m.get("MODEL") + "-financingCommission-" + m.get("STAT_MONTH"), m.get("FINANCING_COMMISSION"));
                map.put(m.get("MODEL") + "-financingServiceFeeMargin-" + m.get("STAT_MONTH"), m.get("FINANCING_SERVICE_FEE_MARGIN"));
                map.put(m.get("MODEL") + "-financingServiceFeeMarginPerCar-" + m.get("STAT_MONTH"), m.get("FINANCING_SERVICE_FEE_MARGIN_PER_CAR"));
                map.put(m.get("MODEL") + "-registrationServiceFeeMargin-" + m.get("STAT_MONTH"), m.get("REGISTRATION_SERVICE_FEE_MARGIN"));
                map.put(m.get("MODEL") + "-otherServiceFeeMargin-" + m.get("STAT_MONTH"), m.get("OTHER_SERVICE_FEE_MARGIN"));
                map.put(m.get("MODEL") + "-totalOtherIncomeMargin-" + m.get("STAT_MONTH"), m.get("TOTAL_OTHER_INCOME_MARGIN"));
                map.put(m.get("MODEL") + "-salesTransactionGpPer-" + m.get("STAT_MONTH"), m.get("SALES_TRANSACTION_GP_PER"));
                map.put(m.get("MODEL") + "-salesDeptHeadcount-" + m.get("STAT_MONTH"), m.get("SALES_DEPT_HEADCOUNT"));
                map.put(m.get("MODEL") + "-salesProductibityPerPerson-" + m.get("STAT_MONTH"), m.get("SALES_PRODUCTIBITY_PER_PERSON"));
                map.put(m.get("MODEL") + "-actual15-" + m.get("STAT_MONTH"), m.get("ACTUAL_GPI_5"));
            }
        } catch (Exception e) {
            logger.error("导出销售管理报表异常", e);
        }
        return map;
    }

    private List getReportByPermitAndYear(String type, String year) {
        return reportRepository.getGroupReportByPermitAndYear(type, year);
    }
}
